package com.kd.dao;
import lombok.Data;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.List;



public interface OrderStatsMapper {
    // 查询方法
    @Select("SELECT date_str, active_count, rev, sale_quantity " +
            "FROM order_stats_daily WHERE date_str = #{dateStr}")
    DailyStats getDailyStats(@Param("dateStr") String dateStr);

    @Select("SELECT month_str, active_count, rev, sale_quantity " +
            "FROM order_stats_monthly WHERE month_str = #{monthStr}")
    MonthlyStats getMonthlyStats(@Param("monthStr") String monthStr);

    @Select("SELECT year_str, active_count, rev, sale_quantity " +
            "FROM order_stats_annual WHERE year_str = #{yearStr}")
    AnnualStats getAnnualStats(@Param("yearStr") String yearStr);

    // 批量查询方法
    @Select("SELECT date_str, active_count, rev, sale_quantity " +
            "FROM order_stats_daily WHERE date_str LIKE CONCAT(#{month}, '%') " +
            "ORDER BY date_str")
    List<DailyStats> getDailyStatsByMonth(@Param("month") String month);

    @Select("SELECT month_str, active_count, rev, sale_quantity " +
            "FROM order_stats_monthly WHERE month_str LIKE CONCAT(#{year}, '%') " +
            "ORDER BY month_str")
    List<MonthlyStats> getMonthlyStatsByYear(@Param("year") String year);

    @Select("SELECT year_str, active_count, rev, sale_quantity " +
            "FROM order_stats_annual WHERE year_str BETWEEN #{startYear} AND #{endYear} " +
            "ORDER BY year_str")
    List<AnnualStats> getAnnualStatsByRange(@Param("startYear") String startYear,
                                            @Param("endYear") String endYear);




    @Update("UPDATE order_stats_daily SET " +
            "active_count = (SELECT COUNT(*) FROM orders WHERE DATE(orderdate) = #{date} AND status > 0), " +
            "rev = (SELECT SUM(totalamount) FROM orders WHERE DATE(orderdate) = #{date} AND status > 0), " +
            "sale_quantity = (SELECT SUM(quantity) FROM order_items oi " +
            "JOIN orders o ON oi.order_id  = o.order_id  " +
            "WHERE DATE(o.orderdate)  = #{date} AND o.status  > 0) " +
            "WHERE date_str = #{dateStr}")
    void updateDailyStats(@Param("date") String date, @Param("dateStr") String dateStr);

    @Insert("INSERT INTO order_stats_monthly (month_str, active_count, rev, sale_quantity) " +
            "SELECT #{monthStr}, " +
            "       COUNT(*) AS active_count, " +
            "       COALESCE(SUM(totalamount), 0) AS rev, " +
            "       COALESCE(SUM(oi.quantity),  0) AS sale_quantity " +
            "FROM orders o " +
            "LEFT JOIN order_items oi ON o.order_id  = oi.order_id  " +
            "WHERE YEAR(o.orderdate)  = SUBSTRING(#{monthStr}, 1, 4) " +
            "  AND MONTH(o.orderdate)  = SUBSTRING(#{monthStr}, 5, 2) " +
            "  AND o.status  > 0 " +
            "ON DUPLICATE KEY UPDATE " +
            "active_count = VALUES(active_count), " +
            "rev = VALUES(rev), " +
            "sale_quantity = VALUES(sale_quantity)")
    void insertMonthlyStats(@Param("monthStr") String monthStr);

    @Insert("INSERT INTO order_stats_annual (year_str, active_count, rev, sale_quantity) " +
            "SELECT #{yearStr}, " +
            "       COUNT(*) AS active_count, " +
            "       COALESCE(SUM(totalamount), 0) AS rev, " +
            "       COALESCE(SUM(oi.quantity),  0) AS sale_quantity " +
            "FROM orders o " +
            "LEFT JOIN order_items oi ON o.order_id  = oi.order_id  " +
            "WHERE YEAR(o.orderdate)  = #{yearStr} AND o.status  > 0 " +
            "ON DUPLICATE KEY UPDATE " +
            "active_count = VALUES(active_count), " +
            "rev = VALUES(rev), " +
            "sale_quantity = VALUES(sale_quantity)")
    void insertAnnualStats(@Param("yearStr") String yearStr);

    // 数据模型
    @Data
    public class DailyStats {
        private String date_str;
        private Integer active_count;
        private BigDecimal rev;
        private Integer sale_quantity;
    }

    @Data
    public class MonthlyStats {
        private String month_str;
        private Integer active_count;
        private BigDecimal rev;
        private Integer sale_quantity;
    }

    @Data
    public class AnnualStats {
        private String year_str;
        private Integer active_count;
        private BigDecimal rev;
        private Integer sale_quantity;
    }
}